package HostComm;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * 
 * @author Dakota Brown, Travis Shuff
 * 
 * Pathway between the Client-to-Physical_Robots and the Client-to-Queue via
 * database connection.
 * 
 */
public class HostDBComm {

    //shared connection by all Robot and Queue Threads
    private static Connection conn;
    private static ResultSet rs;
    private static Statement stmnt;
    Robot robot;
    //SQL Statements to use with PreparedStatements
    //HostDBComm.write():
    String insertHostApplet = "INSERT INTO Arena VALUES(?, ?, ?, ?, ?, ?, ?);";
    String updateHostApplet = "UPDATE Arena SET heading = ?, x = ?, y = ?, "
            + "leftLine = ?, fwdLine = ?, rightLine = ? WHERE "
            + "robot_ID = ?;";
    //HostDBComm.read():
    String selectHeading = "SELECT heading FROM Next_Move WHERE robot_ID = ?;";
    String selectRobot = "SELECT robot_ID FROM Arena WHERE robot_ID = ?;";
    //HostDBComm.checkStamp()
    String updateQueue = "UPDATE Queue SET position = ? WHERE id = ?;";
    String updateQueuePos = "UPDATE Queue SET position = ? WHERE position > ?;";
    //used by Robot Thread methods--read() and write())
    PreparedStatement getRobot;
    PreparedStatement insert;
    PreparedStatement update;
    PreparedStatement getHeading;
    //used by Queue Thread method -- chechTimeStamp()
    PreparedStatement updateQ;
    PreparedStatement updateQPos;

    /**
     * Initializes PreparedStatements for Queue.
     * 
     * @since 09/30/11
     */
    public HostDBComm() {
        try {
            //why are you passing a null pointer here?
            updateQ = conn.prepareStatement(updateQueue);
            updateQPos = conn.prepareStatement(updateQueuePos);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Prepares SQL statements for the Robot.
     * 
     * @param robot Robot added to Host Connection
     * @since 09/30/11
     */
    public HostDBComm(Robot robot) {
        this.robot = robot;
        try {
            if (!conn.isClosed()) {
                insert = conn.prepareStatement(insertHostApplet);
                update = conn.prepareStatement(updateHostApplet);
                getHeading = conn.prepareStatement(selectHeading);
                getRobot = conn.prepareStatement(selectRobot);

            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Takes all new Client IDs with a position of 0 and appends it to the
     * last position in the Queue
     * 
     * @throws SQLException 
     * 
     * @since 10/28/11
     */
    protected void appendQueue() throws SQLException {
        if (!conn.isClosed()) {
            stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
        }

        rs = stmnt.executeQuery("SELECT id, position FROM Queue WHERE position = 0;");
        while (rs.next()) {
            String id = rs.getString(1);
            ResultSet rsMax = stmnt.executeQuery("SELECT Max(position) FROM Queue;");
            rsMax.next();
            int max = rsMax.getInt(1);
            updateQ.setInt(1, (max + 1));
            updateQ.setString(2, id);
            updateQ.executeUpdate();
        }
        rs.close();
    }
    
    /**
     * Removes from Queue all IDs with a Timestamp more than 10 minutes old
     * 
     * @param currentTime - The current time
     * @throws SQLException 
     * 
     * @since 10/28/11
     */

    protected void checkForTimeouts() throws SQLException {
        Timestamp clientTime = null;
        Timestamp currentTime = getTime();
        
        if (!conn.isClosed()) {
            stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
        }

        ResultSet rs2 = stmnt.executeQuery("SELECT * FROM Queue;");

        while (rs2.next()) {
            clientTime = rs2.getTimestamp(3);
            int position = rs2.getInt(2);
            String id = rs2.getString(1);
            if (Math.abs(currentTime.getTime() - clientTime.getTime()) >= 10000) {
                stmnt.execute("DELETE FROM Queue WHERE id='" + id + "';"); //Delete the client that has timed out
                //Repositions all clients behind the client that got deleted.
                rs2 = stmnt.executeQuery("SELECT * FROM Queue;"); // Need to refresh result. DON'T DELETE
                if (rs2 == null) {
                    break;
                }

                updateQPos.setInt(1, (position - 1));
                updateQPos.setInt(2, position);
                updateQPos.execute();
            }
        }
        //removes all clients who have closed the application
        stmnt.execute("DELETE FROM Queue WHERE connection ='f';");
        rs2.close();
    }
    
     /**
     * Checks the timestamp of a Client in the Queue for using the CRIEP platform.
     * If a Client has timed out after 10 minutes, remove them from the queue.
     * 
     * Used by Queue Thread
     * 
     * @since 09/30/11
     */
    protected void checkStamp() {
        try {
            appendQueue();
            checkForTimeouts();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
     /**
     * Deletes tables from database.
     * 
     * Used by disconnect()
     * 
     * @since 09/30/11
     */
    protected static void clear() {
        try {
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }
            stmnt.execute("DELETE FROM Next_Move;");
            stmnt.execute("DELETE FROM Arena;");
            stmnt.execute("DELETE FROM Queue;");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    
    /**
     * Opens a connection to the database and creates necessary tables.
     * 
     * @since 10/18/11
     */
    protected static String connect(HostConnectionObject conObj) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://" + conObj.getHost(), conObj.getUser(), conObj.getPass());
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }

            //HostComm to CRIEP interface
            if (!conn.getMetaData().getTables(null, null, "Host_to_Applet", null).next()) {
                stmnt.execute("CREATE TABLE Arena (robot_id VARCHAR(8), heading VARCHAR(1), x INTEGER, y INTEGER, leftLine INTEGER, fwdLine INTEGER, rightLine INTEGER);");
            }

            //CRIEP interface to HostComm
            if (!conn.getMetaData().getTables(null, null, "Applet_to_Host", null).next()) {
                stmnt.execute("CREATE TABLE Next_Move (robot_id VARCHAR(8), heading VARCHAR(1));");
            }

            //User queue. Position 1 has control over robots
            if (!conn.getMetaData().getTables(null, null, "Queue", null).next()) {
                stmnt.execute("CREATE TABLE Queue (id VARCHAR(11), position INTEGER, timestamp TIMESTAMP, connected CHAR(1))");
            }
            return "Connection Successful";
        } catch (Exception ex) {
            return "Connection Failed";
        }
    }

    /**
     * Gets the current time
     * 
     * @return current time
     * @throws SQLException 
     * 
     * @since 10/28/11
     */
    protected Timestamp getTime() throws SQLException{
                        
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }
            ResultSet time = stmnt.executeQuery("SELECT now();");
            if (time.next()) {
            }

            return time.getTimestamp(1);
    }
    
    
    /**
     *  
     * Called by ActionListener() in Host_View on closing
     * 
     * Clears the database then closes the connection.
     * 
     * @since 09/30/11
     */
    protected static void disconnect() {
        clear();
        try {
            if (!conn.isClosed()) {
                stmnt.close();
                conn.close();
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    
     /**
     * Checks if Connection Object is open
     * @return 
     * 
     * @since 10/18/11
     * 
     */
    protected static boolean isConnected() {
        try {
            return !conn.isClosed();
        } catch (SQLException ex) {
            return false;
        }
    }
    
    

    /**
     * Selects the next move of a given Robot in the form of a Cardinal heading
     * then removes it from the database.
     * 
     * Used by Robot Thread
     * 
     * @return next move in byte[]{1, direction}
     * 
     * @since 09/30/11
     */
    protected byte[] read() {
        try {
            //select heading for robot_ID
            getHeading.setString(1, robot.robotID);
            rs = getHeading.executeQuery();
            if (rs.next()) {
                byte dir = (byte) (rs.getString(1).charAt(0));
                stmnt.execute("DELETE FROM Next_Move;");
                System.out.println("Robot: " + robot.robotID + "   Direction: " + dir);
                return new byte[]{1, dir};
            } else {
                try {
                    Thread.sleep(1000);
                } catch (Exception ex) {
                }
                return null;
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }
    
    
    /**
     *  Writes the Robot's current information to the database.
     * 
     * @since 09/30/11
     */
    protected void write() {
        try {
            //select robot_ID
            getRobot.setString(1, robot.robotID);
            rs = getRobot.executeQuery();
            //if the robot_ID isn't found in DB, insert tuple...
            if (!rs.next()) {
                insert.setString(1, robot.robotID);
                insert.setString(2, String.valueOf(robot.heading));
                insert.setInt(3, robot.point.x);
                insert.setInt(4, robot.point.y);
                insert.setInt(5, robot.leftPath);
                insert.setInt(6, robot.fwdPath);
                insert.setInt(7, robot.rightPath);
                insert.execute();
                System.out.println("Executed insert");
                //otherwise, update existing tuple
            } else {
                update.setString(7, robot.robotID);
                update.setString(1, String.valueOf(robot.heading));
                update.setInt(2, robot.point.x);
                update.setInt(3, robot.point.y);
                update.setInt(4, robot.leftPath);
                update.setInt(5, robot.fwdPath);
                update.setInt(6, robot.rightPath);
                update.execute();
                System.out.println("Executed update");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}
